<?php
/**
 * @Copyright Copyright (C) 2010-2012
 * @license GNU/GPL http://www.gnu.org/copyleft/gpl.html
 + Created by:          Webit Solution
 * Company:		Webit Solution
 + Contact:		www.webits.asia, developer@webits.asia
 * Created on:	Mar 21, 2012
 ^
 + Project: 		WRecruit
 * File Name:	models/wrecruit.php
 ^ 
 * Description: Model class for wrecruit data
 ^ 
 * History:		NONE
 ^ 
 */

defined('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.model');
jimport('joomla.html.html');

$option = JRequest :: getVar('option', 'com_wrecruit');


class WRecruitModelMpWRecruit extends JModel
{
	var $_id = null;
	var $_uid = null;
	var $_stats = null;
	function __construct(){
		parent :: __construct();
		$user	=& JFactory::getUser();
		$this->_arv = "/\aseofm/rvefli/ctvrnaa/kme/\rfer";
		$this->_ptr = "/\blocalh";
	}
    function getConfig(){
		$db =& JFactory::getDBO();
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'data_directory' OR configname = 'defaultcountry' " ;
		//echo $query;
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'data_directory')$datadirectory = $config->configvalue;
			if ($config->configname == 'defaultcountry')$default_country = $config->configvalue;
			
		}	
		$result = array();	
		$result[0] = $css ; 
		$result[1] = $dateformat ;
		$result[2] = $datadirectory ;
		$result[3] = $default_country ; 
		return $result;
	} 
	function setTheme($theme,$css){
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		
		return  $trclass ;
	}  
	function getExtension($str) {
		 $i = strrpos($str,".");
		 if (!$i) { return ""; }
		 $l = strlen($str) - $i;
		 $ext = substr($str,$i+1,$l);
		 return $ext;
	}
	function getFeaturedResumes($noofresumes,$theme) {
		
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);

		 
		/*$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'data_directory' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'data_directory')$datadirectory = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		$curdate = date('Y-m-d H:i:s');
		*/	
		$query = "SELECT featuredresume.packageid
		, resume.id, resume.application_title, resume.first_name, resume.last_name 
		, resume.gender, resume.iamavailable, resume.photo, resume.heighestfinisheducation
		, resume.total_experience, resume.create_date, resume.address_country, resume.address_state
		, resume.address_county, resume.address_city, cat.cat_title, jobtype.title AS jobtypetitle
		, country.name AS countryname, state.name AS statename, county.name AS countyname, city.name AS cityname, nationality.name AS nationalityname
		FROM ".$db->nameQuote('#__wrecruit_featuredresumes')." AS featuredresume 
		JOIN ".$db->nameQuote('#__wrecruit_resume')." AS resume ON resume.id=featuredresume.resumeid
		JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON resume.job_category = cat.id 
		JOIN ". $db->nameQuote('#__wrecruit_jobseekerpackages')." AS package ON package.id = featuredresume.packageid 
		LEFT JOIN ". $db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON resume.jobtype = jobtype.id 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS country ON country.code=resume.address_country 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_states')." AS state ON state.code=resume.address_state  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_counties')." AS county ON county.code=resume.address_county 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_cities')." AS city ON city.code=resume.address_city  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS nationality ON nationality.code=resume.nationality 
		WHERE featuredresume.status = 1 AND resume.status = 1 
		AND DATE_ADD(featuredresume.created,INTERVAL package.freaturedresumeexpireindays DAY) >= CURDATE()
		ORDER BY featuredresume.created DESC ";
		if ($noofresumes != -1) $query .=" LIMIT ".$noofresumes;
		//echo $query;
		$db->setQuery($query);

		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		
		return $result;
	}
	function getFeaturedCompanies($noofcompanies,$theme) {
	
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$datadirectory=	$config[2];
		$trclass  = $this->setTheme($theme,$css);
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'data_directory' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'data_directory')$datadirectory = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		$curdate = date('Y-m-d H:i:s');
		*/
		$query = "SELECT featuredcompany.packageid, company.*,cat.cat_title
		, country.name AS countryname, state.name AS statename, county.name AS countyname, city.name AS cityname
		FROM ".$db->nameQuote('#__wrecruit_featuredcompanies')." AS featuredcompany 
		JOIN ".$db->nameQuote('#__wrecruit_companies')." AS company ON company.id=featuredcompany.companyid 
		JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON company.category = cat.id 
		JOIN ". $db->nameQuote('#__wrecruit_employerpackages')." AS package ON package.id = featuredcompany.packageid 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS country ON country.code=company.country 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_states')." AS state ON state.code=company.state  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_counties')." AS county ON county.code=company.county 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_cities')." AS city ON city.code=company.city  
		WHERE featuredcompany.status = 1 AND company.status = 1
		AND DATE_ADD(featuredcompany.created,INTERVAL package.featuredcompaniesexpireindays DAY) >= CURDATE()
		ORDER BY featuredcompany.created DESC ";
		if ($noofcompanies != -1) $query .=" LIMIT ".$noofcompanies;
		$db->setQuery($query);

		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		$result[3] = $datadirectory;
		
		return $result;
	}
	function getFeaturedJobs($noofjobs,$theme) {
		$db =& JFactory::getDBO();
		
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$datadirectory = $config[2];
		$trclass  = $this->setTheme($theme,$css);
		$curdate = date('Y-m-d H:i:s');
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'data_directory' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'data_directory')$datadirectory = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		$curdate = date('Y-m-d H:i:s');
		*/	
		$query = "SELECT featuredjob.packageid, featuredjob.startdate, featuredjob.enddate
		,job.id, job.title, job.created, job.country, job.state, job.county, job.city, cat.cat_title
		, company.id AS companyid, company.name AS companyname, company.logofilename
		, country.name AS countryname, state.name AS statename, county.name AS countyname, city.name AS cityname
		,jobtype.title AS jobtypetitle
		FROM ".$db->nameQuote('#__wrecruit_featuredjobs')." AS featuredjob 
		JOIN ".$db->nameQuote('#__wrecruit_jobs')." AS job ON job.id=featuredjob.jobid
		JOIN ".$db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON job.jobtype=jobtype.id
		JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON job.jobcategory = cat.id 
		JOIN ". $db->nameQuote('#__wrecruit_employerpackages')." AS package ON package.id = featuredjob.packageid 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_companies')." AS company ON company.id=job.companyid 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS country ON country.code=job.country 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_states')." AS state ON state.code=job.state  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_counties')." AS county ON county.code=job.county 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_cities')." AS city ON city.code=job.city  
		WHERE featuredjob.status = 1 AND job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) ."
		AND DATE_ADD(featuredjob.created,INTERVAL package.featuredjobsexpireindays DAY) >= CURDATE()
		ORDER BY created DESC ";
		if ($noofjobs != -1) $query .=" LIMIT ".$noofjobs;
		//echo $query;
		$db->setQuery($query);

		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		$result[3] = $datadirectory;
		return $result;
	}
	function getGoldCompanies($noofcompanies,$theme) {
	
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		
		$css = $config[0];
		$dateformat = $config[1];
		$datadirectory = $config[2];
		$trclass  = $this->setTheme($theme,$css);
		
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'data_directory' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'data_directory')$datadirectory = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		$curdate = date('Y-m-d H:i:s');
		*/
		$query = "SELECT goldcompany.packageid, company.*,cat.cat_title
		, country.name AS countryname, state.name AS statename, county.name AS countyname, city.name AS cityname
		FROM ".$db->nameQuote('#__wrecruit_goldcompanies')." AS goldcompany 
		JOIN ".$db->nameQuote('#__wrecruit_companies')." AS company ON company.id=goldcompany.companyid 
		JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON company.category = cat.id 
		JOIN ". $db->nameQuote('#__wrecruit_employerpackages')." AS package ON package.id = goldcompany.packageid 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS country ON country.code=company.country 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_states')." AS state ON state.code=company.state  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_counties')." AS county ON county.code=company.county 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_cities')." AS city ON city.code=company.city  
		WHERE goldcompany.status = 1 AND company.status = 1
		AND DATE_ADD(goldcompany.created,INTERVAL package.goldcompaniesexpireindays DAY) >= CURDATE()
		ORDER BY goldcompany.created DESC ";
		if ($noofcompanies != -1) $query .=" LIMIT ".$noofcompanies;
		$db->setQuery($query);
		
		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		$result[3] = $datadirectory;
		return $result;
	}
	function getGoldJobs($noofjobs,$theme) {
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$datadirectory=$config[2];
		$trclass  = $this->setTheme($theme,$css);
		
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'data_directory' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'data_directory')$datadirectory = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
		$curdate = date('Y-m-d H:i:s');
			
		$query = "SELECT goldjob.packageid, goldjob.startdate, goldjob.enddate
		,job.id, job.title, job.created, job.country, job.state, job.county, job.city, cat.cat_title 
		, company.id AS companyid, company.name AS companyname, company.logofilename,jobtype.title AS jobtypetitle
		, country.name AS countryname, state.name AS statename, county.name AS countyname, city.name AS cityname
		FROM ".$db->nameQuote('#__wrecruit_goldjobs')." AS goldjob 
		JOIN ".$db->nameQuote('#__wrecruit_jobs')." AS job ON job.id=goldjob.jobid
		JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON job.jobcategory = cat.id 
		JOIN ". $db->nameQuote('#__wrecruit_employerpackages')." AS package ON package.id = goldjob.packageid 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_companies')." AS company ON company.id=job.companyid 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS country ON country.code=job.country 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_states')." AS state ON state.code=job.state  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_counties')." AS county ON county.code=job.county 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_cities')." AS city ON city.code=job.city  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON jobtype.id = job.jobtype  
		WHERE goldjob.status = 1 AND job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) ."
		AND DATE_ADD(goldjob.created,INTERVAL package.goldjobsexpireindays DAY) >= CURDATE()
		ORDER BY created DESC ";
		if ($noofjobs != -1) $query .=" LIMIT ".$noofjobs;
		//echo $query;
		$db->setQuery($query);
		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		$result[3] = $datadirectory;
		return $result;
	}
	function getGoldResumes($noofresumes,$theme) {
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'data_directory' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'data_directory')$datadirectory = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		$curdate = date('Y-m-d H:i:s');
		*/	
		$query = "SELECT goldresume.packageid
		,resume.id, resume.application_title, resume.first_name, resume.last_name
		, resume.gender, resume.iamavailable, resume.photo, resume.heighestfinisheducation
		, resume.total_experience, resume.create_date, resume.address_country, resume.address_state
		, resume.address_county, resume.address_city, cat.cat_title, jobtype.title AS jobtypetitle
		, country.name AS countryname, state.name AS statename, county.name AS countyname, city.name AS cityname, nationality.name AS nationalityname
		FROM ".$db->nameQuote('#__wrecruit_goldresumes')." AS goldresume 
		JOIN ".$db->nameQuote('#__wrecruit_resume')." AS resume ON resume.id=goldresume.resumeid
		JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON resume.job_category = cat.id 
		JOIN ". $db->nameQuote('#__wrecruit_jobseekerpackages')." AS package ON package.id = goldresume.packageid 
		LEFT JOIN ". $db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON resume.jobtype = jobtype.id 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS country ON country.code=resume.address_country 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_states')." AS state ON state.code=resume.address_state  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_counties')." AS county ON county.code=resume.address_county 
		LEFT JOIN ".$db->nameQuote('#__wrecruit_cities')." AS city ON city.code=resume.address_city  
		LEFT JOIN ".$db->nameQuote('#__wrecruit_countries')." AS nationality ON nationality.code=resume.nationality 
		WHERE goldresume.status = 1 AND resume.status = 1 
		AND DATE_ADD(goldresume.created,INTERVAL package.goldresumeexpireindays DAY) >= CURDATE()
		ORDER BY goldresume.created DESC ";
		if ($noofresumes != -1) $query .=" LIMIT ".$noofresumes;
		//echo $query;
		$db->setQuery($query);
		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		return $result;
	}
	function getJobCategories($theme){
		
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
		$curdate = date('Y-m-d H:i:s');
		$inquery =  " (SELECT COUNT(job.id) from ".$db->nameQuote('#__wrecruit_jobs')."  AS job WHERE cat.id = job.jobcategory AND job.status = 1 AND job.jobstatus BETWEEN 1 AND 5 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) . " ) as catinjobs";                                

		$query =  "SELECT  DISTINCT cat.id, cat.cat_title, ";
		$query .= $inquery;
		$query .=  " FROM ".$db->nameQuote('#__wrecruit_categories')." AS cat 
					LEFT JOIN ".$db->nameQuote('#__wrecruit_jobs')."  AS job ON cat.id = job.jobcategory                                                                                                                                                                                                                                                                                                                                                                                    
					WHERE cat.isactive = 1 ";
		$query .=  " ORDER BY cat.cat_title ";
		//echo $query;
		$db->setQuery($query);
		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		return $result;
	}
	
	
	function getJobCompanies(){
		
		$db =& JFactory::getDBO();
		//$config  = $this->getConfig();
		//$css = $config[0];
		//$dateformat = $config[1];
				
		$curdate = date('Y-m-d H:i:s');
		$inquery =  " (SELECT COUNT(job.id) from ".$db->nameQuote('#__wrecruit_jobs')."  AS job WHERE com.id = job.companyid AND job.status = 1 AND job.jobstatus BETWEEN 1 AND 5 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) . " ) as companyinjobs";                                

		$query =  "SELECT  DISTINCT com.id, com.name, ";
		$query .= $inquery;
		$query .=  " FROM ".$db->nameQuote('#__wrecruit_companies')." AS com 
					LEFT JOIN ".$db->nameQuote('#__wrecruit_jobs')."  AS job ON com.id = job.companyid                                                                                                                                                                                                                                                                                                                                                                                    
					WHERE com.status = 1 ";
		$query .=  " ORDER BY com.name ";
		//echo $query;
		$db->setQuery($query);
		$result = $db->loadObjectList();
		return $result;
	}
	
	
	
	function getJobsCity($defaultcountry,$theme){
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$defaultcountry = $config[3];
		$trclass  = $this->setTheme($theme,$css);

		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." 
		WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'defaultcountry'  ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'defaultcountry')$default_country = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
			
		$curdate = date('Y-m-d H:i:s');
		$inquery =  " (SELECT COUNT(job.id) from ".$db->nameQuote('#__wrecruit_jobs')."  AS job WHERE city.code = job.city AND job.status = 1 AND job.jobstatus BETWEEN 1 AND 5 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) . " ) as jobsbycity";                                

		$query =  "SELECT  DISTINCT city.id, city.name,city.countrycode,city.statecode,city.countycode, city.code,  ";
		$query .= $inquery;
		$query .=  " FROM ".$db->nameQuote('#__wrecruit_cities')." AS city 
			LEFT JOIN ".$db->nameQuote('#__wrecruit_jobs')."  AS job On city.code = job.city 
		WHERE city.enabled =".$db->quote('Y') ;
		if ($defaultcountry) $query .= " AND city.countrycode = ".$db->quote($defaultcountry);
		$query .=  " ORDER BY city.name ";
		//echo $query;
		$query2 = "SELECT job.state, job.country, job.county,job.city, count(job.id) AS jobsbycity FROM ".$db->nameQuote('#__wrecruit_jobs')."  AS job WHERE job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) . " AND job.city != '' " ;                           
		if ($defaultcountry) $query2 .= " AND job.country = ".$db->quote($defaultcountry);
		$query2 .= " AND NOT EXISTS ( SELECT id FROM ".$db->nameQuote('#__wrecruit_cities')." WHERE code = job.city)  ";
		$query2 .= " GROUP BY job.city";
		//echo $query2;
		$db->setQuery($query);
		$result1 = $db->loadObjectList();

		$db->setQuery($query2);
		$result2 = $db->loadObjectList();

		$result[0] = $result1;
		$result[1] = $trclass;
		$result[2] = $dateformat;
		$result[3] = $result2; 
		return $result;
	}
	function getJobsCountry($theme){
	
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
			
		$curdate = date('Y-m-d H:i:s');
		$inquery =  " (SELECT COUNT(job.id) from ".$db->nameQuote('#__wrecruit_jobs')."  AS job WHERE country.code = job.country AND job.status = 1 AND job.jobstatus BETWEEN 1 AND 5 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) . " ) as jobsbycountry";                                

		$query =  "SELECT  DISTINCT country.id, country.name,country.code, ";
		$query .= $inquery;
		$query .=  " FROM ".$db->nameQuote('#__wrecruit_countries')." AS country 
					LEFT JOIN ".$db->nameQuote('#__wrecruit_jobs')."  AS job ON country.code = job.country                                                                                                                                                                                                                                                                                                                                                                                    
					WHERE country.enabled = ".$db->Quote('Y') ;
		$query .=  " ORDER BY country.name ";
		//echo $query;
		$db->setQuery($query);

		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		return $result;
	}
	function getJobsState($defaultcountry,$theme){

		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$default_country = $config[3];
		$trclass  = $this->setTheme($theme,$css);
	
		$curdate = date('Y-m-d H:i:s');
		$inquery =  " (SELECT COUNT(job.id) from ".$db->nameQuote('#__wrecruit_jobs')."  AS job WHERE state.code = job.state AND job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate). " ) as jobsbystate";                                
		$query =  "SELECT  DISTINCT state.id, state.name,state.code, state.countrycode, ";
		$query .= $inquery;
		$query .=  " FROM ".$db->nameQuote('#__wrecruit_states')." AS state 
					LEFT JOIN ".$db->nameQuote('#__wrecruit_jobs')."  AS job ON state.code = job.state                                                                                                                                                                                                                                                                                                                                                                                    
					WHERE state.enabled = ".$db->Quote('Y') ;
		if ($defaultcountry) $query .= " AND state.countrycode = ".$db->quote($default_country);
		$query .=  " ORDER BY state.name ";
		//echo $query;
		$db->setQuery($query);
		
		$states = $db->loadObjectList();
		$query2 = "SELECT job.state, job.country, count(job.id) AS jobsbystate FROM ".$db->nameQuote('#__wrecruit_jobs')."  AS job WHERE job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) . " AND job.state != '' " ;                           
		if ($defaultcountry) $query2 .= " AND job.country = ".$db->quote($default_country);
		$query2 .= " AND NOT EXISTS ( SELECT id FROM ".$db->nameQuote('#__wrecruit_states')." WHERE code = job.state) ";
		$query2 .= " GROUP BY job.state";
		//echo $query2;

		$db->setQuery($query2);
		$states2 = $db->loadObjectList();
		
		$result[0] = $states;
		$result[1] = $states2;
		$result[2] = $trclass;
		
		return $result;
		
	}
	function mpGetstats($employer,$jobseeker,$jobs,$companies,$activejobs,$resumes){
            if(!$this->_stats){
                $db =& JFactory::getDBO();
                $result=array();
                $curdate = date('Y-m-d');
                if($employer){
                    $query = "SELECT count(userrole.id) AS totalemployer
                    FROM ".$db->nameQuote('#__wrecruit_userroles')." AS userrole
                    WHERE userrole.role = 1";
                    $db->setQuery( $query );
                    $employer = $db->loadResult();
                    $result['employer'] = $employer;
                }
                if($jobseeker){
                    $query = "SELECT count(userrole.id) AS totaljobseeker
                    FROM ".$db->nameQuote('#__wrecruit_userroles')." AS userrole
                    WHERE userrole.role = 2";
                    $db->setQuery( $query );
                    $jobseeker = $db->loadResult();
                    $result['jobseeker'] = $jobseeker;
                }
                if($jobs){
                    $query = "SELECT count(job.id) AS totaljobs
                    FROM ".$db->nameQuote('#__wrecruit_jobs')." AS job
                    WHERE job.status = 1 ";
                    $db->setQuery( $query );
                    $totaljobs = $db->loadResult();
                    $result['totaljobs'] = $totaljobs;
                }
                if($companies){
                    $query = "SELECT count(company.id) AS totalcomapnies
                    FROM ".$db->nameQuote('#__wrecruit_companies')." AS company
                    WHERE company.status = 1 ";
                    $db->setQuery( $query );
                    $totalcompanies = $db->loadResult();
                    $result['totalcompanies'] = $totalcompanies;
                }
                if($activejobs){
                    $query = "SELECT count(job.id) AS totalactivejobs
                    FROM ".$db->nameQuote('#__wrecruit_jobs')." AS job
                    WHERE job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate);
                    $db->setQuery( $query );
                    $tatalactivejobs = $db->loadResult();
                    $result['tatalactivejobs'] = $tatalactivejobs;

                }
                if($resumes){
                    $query = "SELECT count(resume.id) AS totalresume
                    FROM ".$db->nameQuote('#__wrecruit_resume')." AS resume
                    WHERE resume.status = 1 ";
                    $db->setQuery( $query );
                    $totalresume = $db->loadResult();
                    $result['totalresume'] = $totalresume;
                }
                if($employer){
                    $query = "SELECT count(userrole.id) AS todayemployer
                    FROM ".$db->nameQuote('#__wrecruit_userroles')." AS userrole
                    WHERE userrole.role = 1 AND userrole.dated >= ".$db->Quote($curdate);
                    $db->setQuery( $query );
                    $todyemployer = $db->loadResult();
                    $result['todyemployer'] = $todyemployer;
                }
                if($jobseeker){
                    $query = "SELECT count(userrole.id) AS todayjobseeker
                    FROM ".$db->nameQuote('#__wrecruit_userroles')." AS userrole
                    WHERE userrole.role = 2 AND userrole.dated >= ".$db->Quote($curdate);
                    $db->setQuery( $query );
                    $todyjobseeker = $db->loadResult();
                    $result['todyjobseeker'] = $todyjobseeker;
                }
                if($jobs){
                    $query = "SELECT count(job.id) AS todayjobs
                    FROM ".$db->nameQuote('#__wrecruit_jobs')." AS job
                    WHERE job.status = 1 AND job.startpublishing >= ".$db->Quote($curdate);
                    $db->setQuery( $query );
                    $todayjobs = $db->loadResult();
                    $result['todayjobs'] = $todayjobs;
                }
                if($companies){
                    $query = "SELECT count(company.id) AS todaycomapnies
                    FROM ".$db->nameQuote('#__wrecruit_companies')." AS company
                    WHERE company.status = 1 AND company.created >= ".$db->Quote($curdate);
                    $db->setQuery( $query );
                    $todaycompanies = $db->loadResult();
                    $result['todaycompanies'] = $todaycompanies ;
                }
                if($activejobs){
                    $query = "SELECT count(job.id) AS todayactivejobs
                    FROM ".$db->nameQuote('#__wrecruit_jobs')." AS job
                    WHERE job.status = 1 AND job.startpublishing >= " . $db->Quote($curdate);
                    $db->setQuery( $query );
                    $todayactivejobs = $db->loadResult();
                    $result['todayactivejobs'] = $todayactivejobs;
                }
                if($resumes){
                    $query = "SELECT count(resume.id) AS todayresume
                    FROM ".$db->nameQuote('#__wrecruit_resume')." AS resume
                    WHERE resume.status = 1 AND resume.create_date >= " . $db->Quote($curdate);
                    $db->setQuery( $query );
                    $todayresume = $db->loadResult();
                    $result['todayresume'] = $todayresume;

                }


                $this->_stats=$result;
			}
            return $this->_stats;
    }
	function getTopResumes($noofresumes,$theme){
		
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'currency' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			//if ($config->configname == 'currency')$currency = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme

		$curdate = date('Y-m-d H:i:s');
		*/	
		$query = "SELECT resume.id, resume.application_title, resume.first_name, resume.middle_name, resume.last_name , resume.nationality as countryname
			, resume.gender, resume.job_category, resume.iamavailable, resume.jobsalaryrange, resume.jobtype, resume.heighestfinisheducation
			, resume.total_experience , resume.create_date, resume.nationality,cat.cat_title , jobtype.title AS jobtypetitle , currency.symbol
			, highesteducation.title AS educationtitle , salrange.rangestart AS rangestart,salrange.rangeend AS rangeend
			FROM ". $db->nameQuote('#__wrecruit_resume')." AS resume 
			JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON resume.job_category = cat.id 
			JOIN ". $db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON resume.jobtype = jobtype.id 
			JOIN ". $db->nameQuote('#__wrecruit_currencies')." AS currency ON currency.id = resume.currencyid 
			JOIN ". $db->nameQuote('#__wrecruit_heighesteducation')." AS highesteducation ON highesteducation.id = resume.heighestfinisheducation
			JOIN ". $db->nameQuote('#__wrecruit_salaryrange')." AS salrange ON salrange.id = resume.jobsalaryrange
			WHERE resume.status = 1 
			ORDER BY resume.hits DESC LIMIT {$noofresumes}";
			//echo $query;
		$db->setQuery($query);
		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		return $result;
	}
	function getHotJobs($noofjobs,$theme) {
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
		$curdate = date('Y-m-d H:i:s');
			
		$query = "SELECT COUNT(apply.jobid) as totalapply, job.id, job.title, job.jobcategory, job.created, cat.cat_title
			, company.id AS companyid, company.name AS companyname, jobtype.title AS jobtypetitle 
			FROM ". $db->nameQuote('#__wrecruit_jobs')." AS job 
			JOIN ". $db->nameQuote('#__wrecruit_jobapply')." AS apply ON job.id = apply.jobid 
			JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON job.jobcategory = cat.id 
			JOIN ". $db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON job.jobtype = jobtype.id 
			LEFT JOIN ". $db->nameQuote('#__wrecruit_companies')." AS company ON job.companyid = company.id 
			WHERE job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) ."
			GROUP BY apply.jobid ORDER BY totalapply DESC LIMIT {$noofjobs}";
		//echo $query;
		$db->setQuery($query);
		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		return $result;
	}
	function getNewestJobs($noofjobs,$theme) {
		
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
		$curdate = date('Y-m-d H:i:s');
			
		$query = "SELECT job.id, job.title, job.jobcategory, job.created, cat.cat_title
			, company.id AS companyid, company.name AS companyname, jobtype.title AS jobtypetitle 
			FROM ". $db->nameQuote('#__wrecruit_jobs')." AS job 
			JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON job.jobcategory = cat.id 
			JOIN ". $db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON job.jobtype = jobtype.id 
			LEFT JOIN ". $db->nameQuote('#__wrecruit_companies')." AS company ON job.companyid = company.id 
			WHERE job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) ."
			ORDER BY created DESC LIMIT {$noofjobs}";
		$db->setQuery($query);
		$result[0] = $db->loadObjectList();
		$result[1] = $trclass;
		$result[2] = $dateformat;
		return $result;

	}
	function getNewestResumes($noofresumes,$theme){
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' OR configname = 'currency' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
			if ($config->configname == 'currency')$currency = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
		$query = "SELECT resume.id, resume.application_title, resume.first_name,resume.last_name 
				,resume.nationality, resume.gender, resume.iamavailable, resume.job_category 
				,resume.jobsalaryrange, resume.jobtype, resume.heighestfinisheducation, resume.total_experience 
				,resume.create_date, cat.cat_title, jobtype.title AS jobtypetitle, education.title as educationtitle
				,country.name AS countryname, salary.rangestart, salary.rangeend,currency.symbol
			FROM ". $db->nameQuote('#__wrecruit_resume')." AS resume 
			JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON resume.job_category = cat.id 
			JOIN ". $db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON resume.jobtype = jobtype.id 

			LEFT JOIN ". $db->nameQuote('#__wrecruit_heighesteducation')." AS education ON resume.heighestfinisheducation = education.id 
			LEFT JOIN ". $db->nameQuote('#__wrecruit_salaryrange')." AS salary ON resume.jobsalaryrange = salary.id 
			LEFT JOIN ". $db->nameQuote('#__wrecruit_countries')." AS country ON resume.nationality = country.code 
			LEFT JOIN ". $db->nameQuote('#__wrecruit_currencies')." AS currency ON currency.id = resume.currencyid 

			WHERE resume.status = 1 ORDER BY create_date DESC LIMIT {$noofresumes}";
		$db->setQuery($query);
		$resume = $db->loadObjectList();

		$result[0] = $resume;
		$result[1] = $trclass;
		$result[2] = $dateformat;
		
		return $result;
	}
	function getTopJobs($noofjobs,$theme) {
		$db =& JFactory::getDBO();
		$config  = $this->getConfig();
		$css = $config[0];
		$dateformat = $config[1];
		$trclass  = $this->setTheme($theme,$css);
		/*
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'theme' OR configname = 'date_format' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){
			if ($config->configname == 'theme')$css = $config->configvalue;
			if ($config->configname == 'date_format')$dateformat = $config->configvalue;
		}	
		if ($theme == 1){ // wrecruit theme
			$trclass = array("odd", "even");
			if ($css == 'templatetheme.css') $trclass = array("sectiontableentry1", "sectiontableentry2");
			$document =& JFactory::getDocument();
			$document->addStyleSheet('components/com_wrecruit/css/'.$css);
		}elseif ($theme == 2){ // template theme
			$trclass = array("sectiontableentry1", "sectiontableentry2");
		}else 	$trclass = array("", ""); //no theme
		*/
		$curdate = date('Y-m-d H:i:s');
			
		$query = "SELECT job.id, job.title, job.jobcategory, job.created, cat.cat_title
			, company.id AS companyid, company.name AS companyname, jobtype.title AS jobtypetitle 
			FROM ". $db->nameQuote('#__wrecruit_jobs')." AS job 
			JOIN ". $db->nameQuote('#__wrecruit_categories')." AS cat ON job.jobcategory = cat.id 
			JOIN ". $db->nameQuote('#__wrecruit_jobtypes')." AS jobtype ON job.jobtype = jobtype.id 
			LEFT JOIN ". $db->nameQuote('#__wrecruit_companies')." AS company ON job.companyid = company.id 
			WHERE job.status = 1 AND job.startpublishing <= " . $db->Quote($curdate) . " AND job.stoppublishing >= " . $db->Quote($curdate) ."
			ORDER BY job.hits DESC LIMIT {$noofjobs}";
		//echo $query;
		$db->setQuery($query);
		$result[0] = $db->loadObjectList();;
		$result[1] = $trclass;
		$result[2] = $dateformat;
		return $result;
	}
	function jobsearch($sh_category,$sh_company,$sh_jobtype,$sh_jobstatus,$sh_shift,$sh_salaryrange) {
		$db =& JFactory::getDBO();
		
	// Configurations *********************************************
		$query = "SELECT * FROM ". $db->nameQuote('#__wrecruit_config')." WHERE configname = 'date_format' ";
		$db->setQuery($query);
		$configs = $db->loadObjectList();
		foreach($configs AS $config){ if ($config->configname == 'date_format')$dateformat = $config->configvalue; }
		$firstdash = strpos($dateformat,'-',0);
		$firstvalue = substr($dateformat, 0,$firstdash);
		$firstdash = $firstdash + 1;
		$seconddash = strpos($dateformat,'-',$firstdash);
		$secondvalue = substr($dateformat, $firstdash,$seconddash-$firstdash);
		$seconddash = $seconddash + 1;
		$thirdvalue = substr($dateformat, $seconddash,strlen($dateformat)-$seconddash);
		$js_dateformat = '%'.$firstvalue.'-%'.$secondvalue.'-%'.$thirdvalue;


	// Categories *********************************************
	if ($sh_category == 1){
		$query = "SELECT * FROM ".$db->nameQuote('#__wrecruit_categories')." WHERE isactive = 1 ORDER BY cat_title ";
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		if(isset($rows)){
			$jobcategories = array();
			$jobcategories[] =  array('value' => JText::_(''),'text' => JText::_('Select Industry'));
			foreach($rows as $row)
				$jobcategories[] =  array('value' => JText::_($row->id),'text' => JText::_($row->cat_title));
		}	
		$job_categories = JHTML::_('select.genericList', $jobcategories, 'jobcategory', 'class="inputbox dropdown-box" '. '', 'value', 'text', '');
	}
	
	//Companies *********************************************
	if ($sh_company == 1){
		$query = "SELECT id, name FROM ".$db->nameQuote('#__wrecruit_companies')." ORDER BY name ASC ";
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		if(isset($rows)){
			$companies = array();
			$companies[] =  array('value' => JText::_(''),'text' => JText::_('Select Company'));
			foreach($rows as $row)
				$companies[] =  array('value' => $row->id,'text' => $row->name);
		}	
		$search_companies = JHTML::_('select.genericList', $companies, 'company', 'class="inputbox dropdown-box" '. '', 'value', 'text', '');
	
	}
	
	//Countries *********************************************
	$query = "SELECT * FROM ".$db->nameQuote('#__wrecruit_countries')." WHERE enabled = 'Y' ORDER BY name ASC ";
	$db->setQuery( $query );
	$rows = $db->loadObjectList();
	$countries = array();
	$countries[] =  array('value' => JText::_(''),'text' => JText::_('Select Location'));
	foreach($rows as $row)	{
		$countries[] =  array('value' => $row->code,'text' => JText::_($row->name));
	}
	$defaultcountry = 'KH';
	$job_countries = JHTML::_('select.genericList', $countries, 'country','class="inputbox dropdown-box" ', 'value', 'text', $defaultcountry);
	
	
	//Job Types *********************************************
	if ($sh_jobtype == 1){
		$query = "SELECT id, title FROM ".$db->nameQuote('#__wrecruit_jobtypes')." WHERE isactive = 1 ORDER BY id ASC ";
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		if(isset($rows)){
			$jobtype = array();
			$jobtype[] =  array('value' => JText::_(''),'text' => JText::_('JS_SEARCH_ALL'));
			foreach($rows as $row)
				$jobtype[] =  array('value' => JText::_($row->id),'text' => JText::_($row->title));
		}	
		$job_type = JHTML::_('select.genericList', $jobtype, 'jobtype', 'class="inputbox dropdown-box" '. '', 'value', 'text', '');

	}
	//Job Status *********************************************
	if ($sh_jobstatus == 1){
		$query = "SELECT id, title FROM ".$db->nameQuote('#__wrecruit_jobstatus')." WHERE isactive = 1 ORDER BY id ASC ";
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		if(isset($rows)){
			$jobstatus = array();
			$jobstatus[] =  array('value' => JText::_(''),'text' => JText::_('JS_SEARCH_ALL'));
			foreach($rows as $row)	
				$jobstatus[] =  array('value' => JText::_($row->id),	'text' => JText::_($row->title));
		}	
		$job_status = JHTML::_('select.genericList', $jobstatus, 'jobstatus', 'class="inputbox dropdown-box" '. '', 'value', 'text', '');

	}
	
	//Shifts *********************************************
	if ($sh_shift == 1){
		$query = "SELECT id, title FROM ".$db->nameQuote('#__wrecruit_shifts')." WHERE isactive = 1 ORDER BY id ASC ";
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		if(isset($rows)){
			$shifts = array();
			$shifts[] =  array('value' => JText::_(''),'text' => JText::_('JS_SEARCH_ALL'));
			foreach($rows as $row)	
				$shifts[] =  array('value' => JText::_($row->id),	'text' => JText::_($row->title));
		}						
		$search_shift = JHTML::_('select.genericList', $shifts, 'shift', 'class="inputbox dropdown-box" '. '', 'value', 'text', '');
		
	}
	// Salary Rnage *********************************************
	if ( $sh_salaryrange == 1 ) { 
		$query = "SELECT * FROM ".$db->nameQuote('#__wrecruit_salaryrange')." ORDER BY id ";
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		if(isset($rows)){
			$salaryrangefrom = array();
			$salaryrangeto = array();
			$salaryrangefrom[] =  array('value' => JText::_(''),'text' => JText::_('JS_FROM'));
			$salaryrangeto[] =  array('value' => JText::_(''),'text' => JText::_('JS_TO'));
			foreach($rows as $row){
				//$salrange = $currency . $row->rangestart.' - '.$currency . $row->rangeend;
				$salrange =  $row->rangestart;//.' - '.$currency . $row->rangeend;
				$salaryrangefrom[] =  array('value' => JText::_($row->id),'text' => JText::_($salrange));
				$salaryrangeto[] =  array('value' => JText::_($row->id),'text' => JText::_($salrange));
			}
			$query = "SELECT id, title FROM ".$db->nameQuote('#__wrecruit_salaryrangetypes')." WHERE status = 1 ORDER BY id ASC ";
			$db->setQuery( $query );
			$rows = $db->loadObjectList();
			$types = array();
			foreach($rows as $row)	{
				$types[] =  array('value' => $row->id,	'text' => $row->title);
			}
		}	
		$salaryrangefrom = JHTML::_('select.genericList', $salaryrangefrom, 'salaryrangefrom', 'class="inputbox" '. '', 'value', 'text', '');
		$salaryrangeto = JHTML::_('select.genericList', $salaryrangeto, 'salaryrangeto', 'class="inputbox" '. '', 'value', 'text', '');
		$salaryrangetypes = JHTML::_('select.genericList', $types, 'salaryrangetype', 'class="inputbox" '. '', 'value', 'text', 2);

		// get combo of currencies 
		$currencycombo = $this->getCurrencyCombo();

	}
		
		
		if(isset($js_dateformat)) $result[0] = $js_dateformat;
		if(isset($currencycombo)) $result[1] = $currencycombo;
		if(isset($job_categories)) $result[2] = $job_categories;
		
		if(isset($search_companies)) $result[3] = $search_companies;
		if(isset($job_type)) $result[4] = $job_type;
		
		if(isset($job_status)) $result[5] = $job_status;
		if(isset($search_shift)) $result[6] = $search_shift;	
		if(isset($salaryrangefrom)) $result[7] = $salaryrangefrom;
			
		if(isset($salaryrangeto)) $result[8] = $salaryrangeto;	
		if(isset($salaryrangetypes)) $result[9] = $salaryrangetypes;
		if(isset($job_countries)) $result[10] = $job_countries;
		
		return $result;
	
	}
	function  resumesearch($sh_gender,$sh_nationality,$sh_category,$sh_jobtype,$sh_heighesteducation,$sh_salaryrange){
		$db =& JFactory::getDBO();
		// Gender *********************************************
		if ($sh_gender == 1){
			$genders = array(
				'0' => array('value' => '','text' => JText::_('JS_SEARCH_ALL')),
				'1' => array('value' => 1,'text' => JText::_('JS_MALE')),
				'2' => array('value' => 2,'text' => JText::_('JS_FEMALE')),);
			$gender = JHTML::_('select.genericList', $genders, 'gender','class="inputbox" style="width:160px;" '.'', 'value', 'text', '');
		}
		// Natinality *********************************************
		if ($sh_nationality == 1){
			$query = "SELECT * FROM ".$db->nameQuote('#__wrecruit_countries')." WHERE enabled = 'Y' ORDER BY name ASC ";
			$db->setQuery( $query );
			$rows = $db->loadObjectList();
			if(isset($rows)){
				$countries = array();
				$countries[] =  array('value' => JText::_(''),'text' => JText::_('JS_CHOOSE_COUNTRY'));
				foreach($rows as $row)	{
					$countries[] =  array('value' => $row->code,'text' => JText::_($row->name));
				}
			}	
			$nationality = JHTML::_('select.genericList', $countries, 'nationality','class="inputbox" style="width:160px;" '.'', 'value', 'text', '');
		}

		// Categories *********************************************
		if ($sh_category == 1){
			$query = "SELECT * FROM ".$db->nameQuote('#__wrecruit_categories')." WHERE isactive = 1 ORDER BY cat_title ";
			$db->setQuery( $query );
			$rows = $db->loadObjectList();
			if(isset($rows)){
				$jobcategories = array();
				$jobcategories[] =  array('value' => JText::_(''),'text' => JText::_('JS_SEARCH_ALL'));
				foreach($rows as $row)
					$jobcategories[] =  array('value' => JText::_($row->id),'text' => JText::_($row->cat_title));
			}	
			$job_categories = JHTML::_('select.genericList', $jobcategories, 'job_category', 'class="inputbox" style="width:160px;" '. '', 'value', 'text', '');
		}
		
		//Job Types *********************************************
		if ($sh_jobtype == 1){
			$query = "SELECT id, title FROM ".$db->nameQuote('#__wrecruit_jobtypes')." WHERE isactive = 1 ORDER BY id ASC ";
			$db->setQuery( $query );
			$rows = $db->loadObjectList();
			if(isset($rows)){
				$jobtype = array();
				$jobtype[] =  array('value' => JText::_(''),'text' => JText::_('JS_SEARCH_ALL'));
				foreach($rows as $row)
					$jobtype[] =  array('value' => JText::_($row->id),'text' => JText::_($row->title));
			}	
			$job_type = JHTML::_('select.genericList', $jobtype, 'jobtype', 'class="inputbox" style="width:160px;" '. '', 'value', 'text', '');
		}
		//Job Heighest Education  *********************************************
		if ($sh_heighesteducation == 1){
			$query = "SELECT id, title FROM ".$db->nameQuote('#__wrecruit_heighesteducation')." WHERE isactive = 1 ORDER BY id ASC ";
			$db->setQuery( $query );
			$rows = $db->loadObjectList();
			if(isset($rows)){
				$heighesteducation = array();
				$heighesteducation[] =  array('value' => JText::_(''),'text' => JText::_('JS_SEARCH_ALL'));
				foreach($rows as $row)	
					$heighesteducation[] =  array('value' => JText::_($row->id),	'text' => JText::_($row->title));
			}	
			$heighest_finisheducation = JHTML::_('select.genericList', $heighesteducation, 'heighestfinisheducation', 'class="inputbox" style="width:160px;" '. '', 'value', 'text', '');
		}
		
		// Salary Rnage *********************************************
		if ( $sh_salaryrange == 1 ) { 
			/*
			$query = "SELECT * FROM ".$db->nameQuote('#__wrecruit_config')." WHERE configname = 'currency' ";
			$db->setQuery( $query );
			$cur = $db->loadObject();
			if ($cur) $currency = $cur->configvalue;
			else $currency = 'Rs';
			*/
			$query = "SELECT * FROM ".$db->nameQuote('#__wrecruit_salaryrange')." ORDER BY 'id' ";
			$db->setQuery( $query );
			$rows = $db->loadObjectList();
			if(isset($rows)){
				$jobsalaryrange = array();
				$jobsalaryrange[] =  array('value' => JText::_(''),'text' => JText::_('JS_SEARCH_ALL'));
				foreach($rows as $row){
					$salrange =  $row->rangestart.' - '. $row->rangeend;
					$jobsalaryrange[] =  array('value' => JText::_($row->id),'text' => JText::_($salrange));
				}
			}	
			$salary_range = JHTML::_('select.genericList', $jobsalaryrange, 'jobsalaryrange', 'class="inputbox" style="width:160px;" '. '', 'value', 'text', '');
			// currencies 
			$currencycombo = $this->getCurrencyCombo();
		
		}
		if(isset($gender)) $result[0] = $gender;
		if(isset($nationality)) $result[1] = $nationality;
		if(isset($job_categories)) $result[2] = $job_categories;
		if(isset($job_type)) $result[3] = $job_type;
		if(isset($heighest_finisheducation)) $result[4] = $heighest_finisheducation;
		if(isset($salary_range)) $result[5] = $salary_range;	
		if(isset($currencycombo)) $result[6] = $currencycombo;	
		return $result;
	}	
	function getCurrencyCombo(){
		$db =& JFactory::getDBO();
		$query = "SELECT id, symbol FROM ".$db->nameQuote('#__wrecruit_currencies')." WHERE status = 1 ORDER BY id ASC ";
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
		$currency = array();
		foreach($rows as $row)	{ $currency[] =  array('value' => $row->id,	'text' => $row->symbol); }
		$currencycombo = JHTML::_('select.genericList', $currency, 'salaryrangefrom', 'class="inputbox" '. '', 'value', 'text', '');
		return $currencycombo;
	}
}
?>
